/**
 * @author yaron
 */

//$.getScript('utils.js');

var dbFile = Titanium.Filesystem.getFile(Titanium.Filesystem.getResourcesDirectory() + "/sengles.db");


getDB = function(){
	var db = Titanium.Database.openFile(dbFile);
	return db;
}


getSysconfig = function(db, key){
	var base_sql = "select * from sys_config";
	if (key){
		base_sql = base_sql + ' where key="{0}"'.format(key);
	}
	var dbrows = db.execute(base_sql);
	var sysconfig = {};
	while(dbrows.isValidRow()){
		sysconfig[dbrows.fieldByName('key')] = eval(dbrows.fieldByName('value'));
		dbrows.next();
	}
	return sysconfig;
}


generateProductsQueryCondition = function(base_sql, args){
	var args = arguments[1] ? arguments[1] : {};
	var orderby = args.hasOwnProperty('orderby') ? args['orderby'] : 'created_at desc';
	var condition = args.hasOwnProperty('condition') ? args['condition'] : '';
	
	if (condition){
		var arr = [];
		category = condition.hasOwnProperty('category') ? condition['category'] : '';
		sub_category = condition.hasOwnProperty('sub_category') ? condition['sub_category'] : '';
		keyword = condition.hasOwnProperty('keyword') ? condition['keyword'] : '';
		if (category){
			arr.push('category="{0}"'.format(category));
		}
		if (sub_category){
			arr.push('sub_category="{0}"'.format(sub_category));
		}
		if (keyword){
			arr.push('name like "%{0}%"'.format(keyword));
		}
		base_sql = base_sql + ' where ' + arr.join(' and ');
	}
	
	if (orderby){
		base_sql = base_sql + ' order by {0}'.format(orderby);
	}
	
	return base_sql;
}

/*
 * support find by keyword, category, subcategory
 */
getProductes = function(db, args){
	var args = arguments[1] ? arguments[1] : {};
	var base_sql = "select * from product";
	base_sql = generateProductsQueryCondition(base_sql, args);
	var page = args.hasOwnProperty('page') ? args['page'] : 1;
	var limit = args.hasOwnProperty('limit') ? args['limit'] : 2;
	var offset = (page - 1) * limit;
	base_sql = base_sql + ' limit {0},{1}'.format(offset.toString(), limit);

	var dbrows = db.execute(base_sql);
	var productes = new Array();
	while(dbrows.isValidRow()){
		productes.push({
			id:dbrows.fieldByName('id'),
			barcode: dbrows.fieldByName('barcode'),
			name: dbrows.fieldByName('name'),
			stock: dbrows.fieldByName('stock'),
			specification: dbrows.fieldByName('specification'),
			category: dbrows.fieldByName('category'),
			stock_count: dbrows.fieldByName('stock_count'),
			sub_category: dbrows.fieldByName('sub_category'),
			tag: dbrows.fieldByName('tag')});
		dbrows.next();
	}
	return productes;
}


getTotalpagesOfProduct = function(db, args){
	// 20 rows per page
	limit = 2;
	base_sql = 'select count(*) from product';
	base_sql = generateProductsQueryCondition(base_sql, args);
	dbrows = db.execute(base_sql);
	count = 0;
	if (dbrows.isValidRow()){
		count = dbrows.field(0);
	}
	if (count % limit){
		page = parseInt(count / limit) + 1;
	}else {
		page = parseInt(count/limit);
	}
	if (!page){
		page = 1;
	}
	return page;
}


addCategory = function(db, category){
	// category = {'name':'xx', 'parent':''};

	return true;
}


isProductExisted = function(db, name){
	base_url = 'SELECT * FROM product where name="{0}"'.format(name);
	rs = db.execute(base_url);
	if (rs.isValidRow()) { // OR rows.isValidRow()
		return true;
	}
	return false;	
}

addProduct = function(db, product){
	// product = {'name':'xxx', 'category':'', 'sub_category':''};
	if (! isProductExisted(db, product.name)){
		base_url = "INSERT INTO product(barcode, name, stock, specification, category, sub_category, tag) " 
			+ "VALUES('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}')".format(product.barcode, 
				product.name, product.stock, product.specification, product.category, product.sub_category, product.tag);
		db.execute(base_url);
		rs = db.execute('select * from product where name="{0}"'.format(product.name));
		if (rs.isValidRow()) { // OR rows.isValidRow()
			return rs.fieldByName('id');
		}
		return 0;
	}
	return 0;
}
